/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dictionary.dal;

import dictionary.entities.Classify;
import dictionary.entities.Word;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author s0c0labd
 */
public class ClassifyDAO {

    private Connection cn;

    public ClassifyDAO() {
    }

    public Classify getByName(String s) {
        cn = ConnectDB.getConnection();
        Classify c = new Classify();
        try {
            PreparedStatement pstm = cn.prepareStatement("SELECT ClassifyName FROM Classify WHERE ClassifyName = ?");
            pstm.setString(1, s);
            ResultSet rs = pstm.executeQuery();
            c.setClassify(rs.getString(1));
            rs.close();
            pstm.close();
            cn.close();
        } catch (SQLException ex) {
            Logger.getLogger(ClassifyDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return c;
    }

    public List<Classify> getByWord(Word w) {
        cn = ConnectDB.getConnection();
        List<Classify> l = new ArrayList();
        try {
            CallableStatement cstm1 = cn.prepareCall("{call spGetWordID(?,?,?)}");
            cstm1.registerOutParameter(3, Types.INTEGER);
            cstm1.setString(1, w.getWord());
            cstm1.setInt(2, w.getType());
            cstm1.execute();
            int wordID = cstm1.getInt(3);
            cstm1.close();

            if (wordID != -1) {
                PreparedStatement pstm = cn.prepareStatement("SELECT ClassifyID FROM Mean WHERE WordID = ?");
                pstm.setInt(1, wordID);
                ResultSet rs = pstm.executeQuery();
                while (rs.next()) {
                    CallableStatement cstm2 = cn.prepareCall("{call spGetClassifyName(?,?,?)}");
                    cstm2.registerOutParameter(2, Types.NVARCHAR);
                    cstm2.registerOutParameter(3, Types.INTEGER);
                    cstm2.setInt(1, rs.getInt("ClassifyID"));
                    cstm2.execute();
                    l.add(new Classify(cstm2.getString(2),w.getType()));
                    cstm2.close();
                }
                rs.close();
                pstm.close();
            }
            cn.close();
        } catch (SQLException ex) {
            Logger.getLogger(ClassifyDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return l;
    }

    public List<Classify> getAll(int type) {
        cn = ConnectDB.getConnection();
        List<Classify> l = new ArrayList<Classify>();
        try {
            PreparedStatement pstm = cn.prepareStatement("SELECT ClassifyName FROM Classify WHERE TypeID = ? ORDER BY ClassifyID DESC");
            pstm.setInt(1, type);
            ResultSet rs = pstm.executeQuery();
            while (rs.next()) {
                l.add(new Classify(rs.getString(1),type));
            }
            rs.close();
            pstm.close();
            cn.close();
        } catch (SQLException ex) {
            Logger.getLogger(ClassifyDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return l;
    }

    public int insertClassify(Classify c) {
        cn = ConnectDB.getConnection();

        int i = 2;
        try {
            CallableStatement cstm = cn.prepareCall("{call spInsertClassify(?,?,?)}");
            cstm.registerOutParameter(3, Types.INTEGER);
            cstm.setString(1, c.getClassify());
            cstm.setInt(2, c.getType());
            cstm.execute();
            i = cstm.getInt(3);
            cstm.close();
            cn.close();
        } catch (SQLException ex) {
            Logger.getLogger(ClassifyDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return i;
    }

    public int deleteClassify(Classify c) {
        cn = ConnectDB.getConnection();
        int i = 3;
        try {
            CallableStatement cstm = cn.prepareCall("{call spDeleteClassify(?,?,?)}");
            cstm.registerOutParameter(3, Types.INTEGER);
            cstm.setInt(2, c.getType());
            cstm.setString(1, c.getClassify());
            cstm.execute();
            i = cstm.getInt(3);
            cstm.close();
            cn.close();
        } catch (SQLException ex) {
            Logger.getLogger(ClassifyDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return i;
    }

    public int updateClassify(Classify c, Classify c1) {
        cn = ConnectDB.getConnection();
        int i = 3;
        if (c.getType() != c1.getType()) {
            i = 2;
        } else {
            try {
                CallableStatement cstm = cn.prepareCall("{call spUpdateClassify(?,?,?,?)}");
                cstm.registerOutParameter(4, Types.INTEGER);
                cstm.setString(1, c.getClassify());
                cstm.setString(2, c1.getClassify());
                cstm.setInt(3, c.getType());
                cstm.execute();
                i = cstm.getInt(4);
                cstm.close();
                cn.close();
            } catch (SQLException ex) {
                Logger.getLogger(ClassifyDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return i;
    }

//    public static void main(String[] args) {
//        ClassifyDAO c = new ClassifyDAO();
//        int i = c.deleteClassify(new Classify("a", 1));
//        System.out.println(i);
//    }
}
